package co.yixiang.modules.shop.repository;

import co.yixiang.modules.shop.domain.YxStoreCategory;
import co.yixiang.modules.shop.domain.YxStoreProduct;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Objects;

/**
 * @author hupeng
 * @date 2019-10-04
 */
public interface YxStoreProductRepository extends JpaRepository<YxStoreProduct, Integer>, JpaSpecificationExecutor {
    @Modifying
    @Query(value = "update yx_store_product set is_stock_mall = ?1, is_mall_on=0 where id = ?2", nativeQuery = true)
    void updateOnsale(int status, int id);

    @Modifying
    @Query(value = "update yx_store_product set is_del = ?1 where id = ?2", nativeQuery = true)
    void updateDel(int status, int id);

    List<YxStoreProduct> findByStoreCategoryAndIsDel(YxStoreCategory storeCategory, int isDel);

    @Query(value = "select * from yx_store_product where (cost=0 or cost is null) and is_del=0 and is_show=0", nativeQuery = true)
    List<YxStoreProduct> findAllPriceNull();

    @Query(value = "select * from yx_store_product wehre id IN (?1)", nativeQuery = true)
    List<YxStoreProduct> findArryById(String id);

    /**
     * 更新商品为是否投资商品库商品
     *
     * @param isStockInvest
     * @param id
     */
    @Modifying
    @Query(value = "update yx_store_product set is_stock_invest = ?1,is_invest_on=?3 where id = ?2", nativeQuery = true)
    void updateonSaleInvest(int isStockInvest, Integer id, int isInvestOn);

    /**
     * 更新保留状态
     *
     * @param id
     * @param lockStatus
     * @return
     */
    @Modifying
    @Query(value = "update yx_store_product set lock_Status=?2 where id=?1", nativeQuery = true)
    int updateLockStatus(String id, int lockStatus);

    /**
     * 查询到期锁定的商品
     *
     * @return
     */
    @Query(value = "select * from yx_store_product where lock_status =1 and is_invested =0 and  lock_release_date_time < now()", nativeQuery = true)
    List<YxStoreProduct> findGoodsLock();

    /**
     * 通过品牌名称查询品牌ID
     *
     * @param brandName
     * @return
     */
    @Query(value = "select id from yx_store_brand where brand_name=?1 limit 1", nativeQuery = true)
    Integer findBrandId(String brandName);

    @Query(value = "select de.id from dict d inner join dict_detail de on d.id=de.dict_id where d.name=?1 and label=?2", nativeQuery = true)
    Integer findDetailId(String name, String label);

    @Query(value = "select * from yx_store_product where store_id=?1 and  store_name=?2 and supplier_code=?3 limit 1", nativeQuery = true)
    YxStoreProduct findByGoods(String storeId, String storeName, String supplierCode);

    /**
     * 根据条码查询商品
     *
     * @param barCode
     * @return
     */
    @Query(value = "select * from yx_store_product where bar_code like concat('%',?1,'%')",nativeQuery = true)
    List<YxStoreProduct> findByBarCode(String barCode);

    /**
     * 商城商品上架/下架
     *
     * @param id
     * @param isMallOn
     */
    @Modifying
    @Query(value = "update yx_store_product set is_Mall_On = ?1 where id = ?2", nativeQuery = true)
    void updateOnMallSale(Integer isMallOn, Integer id);

    /**
     * 通过商品名,门店查询商品
     *
     * @param storeId   门店ID
     * @param storeName 商品名称
     * @return
     */
    @Query(value = "select * from yx_store_product where store_id=?1 and  store_name=?2  limit 1", nativeQuery = true)
    YxStoreProduct findByStoreGoods(String storeId, String storeName);

    /**
     * 查询带分页库存盘点
     *
     * @param beginTime
     * @param endTime
     * @param pageable
     * @return
     */
    @Query(value = "select yx.id,yx.store_name AS storeName,yx.unit_Name AS unitName,ifnull(yx.invest_cost,0) AS investCost," +
            " yx.stock,yxs.shelf_num AS shelfNum,yxs.stock as checkStock,ifnull(yx.stock,0)-ifnull(yxs.stock,0) as differeNumber," +
            " (ifnull(yx.stock,0)-ifnull(yxs.stock,0))*ifnull(yx.invest_cost,0) as differeAmount,yx.add_time as yxAddTime,yxs.adder as adder," +
            " ''  as checkPeople,''  as offerorSmoke,yxs.add_time as addTime from yx_store_product yx INNER JOIN yx_store_product_checkmake yxs ON yx.id = yxs.product_id " +
            " where if(?1 !='',yxs.add_Time BETWEEN ?1 and ?2,1=1) order by yx.store_name",
            countQuery = "select count(*) from yx_store_product yx INNER JOIN yx_store_product_checkmake yxs ON yx.id = yxs.product_id" +
                    " where if(?1 !='',yxs.add_Time BETWEEN ?1 and ?2,1=1)", nativeQuery = true)
    Page<Map> getStockTaking(String beginTime, String endTime, Pageable pageable);

    /**
     * 查询不分页库存盘点
     *
     * @param beginTime
     * @param endTime
     * @return
     */
    @Query(value = "select yx.id,yx.store_name AS storeName,yx.unit_Name AS unitName,ifnull(yx.invest_cost,0) AS investCost," +
            " yx.stock,yxs.shelf_num AS shelfNum,yxs.stock as checkStock,ifnull(yx.stock,0)-ifnull(yxs.stock,0) as differeNumber," +
            " (ifnull(yx.stock,0)-ifnull(yxs.stock,0))*ifnull(yx.invest_cost,0) as differeAmount,yxs.adder as adder," +
            " ''  as checkPeople,''  as offerorSmoke,yx.add_time as addTime from yx_store_product yx INNER JOIN yx_store_product_checkmake yxs ON yx.id = yxs.product_id " +
            " where if(?1 !='',yxs.add_Time BETWEEN ?1 and ?2,1=1) order by yx.store_name",nativeQuery = true)
    List<Map<String,Object>> stockTakingList(String beginTime, String endTime);

    /**
     * 查询已认购的商品信息
     * @return
     */
    @Query(value = "select yx.id,yxs.cate_code as cateCode,yxs.cate_name as cateName,yx.store_name as storeName,yx.place," +
            "yx.brand_name as brandNname,yx.unit_name as unitName,yx.mini_quantity as miniQuantity,yx.cost," +
            "yx.purchase_price as purchasePrice,yx.price,yx.invest_price  as investPrice," +
            "(select suk from yx_store_product_attr_value yxa inner join yx_store_product yxp on yxa.product_id=yxp.id order by yxa.id LIMIT 1) as suk " +
            " from yx_store_product yx INNER JOIN yx_store_category yxs ON yx.cate_id = yxs.id " +
            " where if(?1!='',yx.store_name like concat('%',?1,'%'),1=1) and if(?2!='',yx.is_invested=?2,1=1) " +
            " and if(?3!='',yx.is_hot=?3,1=1) and yx.is_stock_invest=1 and yx.is_del=0 and yx.is_invest_on=1",nativeQuery = true)
    List<Map<String,Objects>> findGoodsSubscript(String storeName,String isInvested,String isHot);

    /**
     * 更新促销价
     * @param productId
     * @param promotePrice
     */
    @Modifying
    @Query(value = "update  yx_store_product set promote_price=?2," +
            "promote_begin_time=?3,promote_end_time=?4 where id=?1",nativeQuery = true)
    void updatePromotePrice(Integer productId, BigDecimal promotePrice, Date beginTime, Date endTime);

    /**
     * 查询可以参与促销的商品
     * @param storeName
     * @param cateId
     * @param beginTime
     * @param endTime
     * @param pageable
     * @return
     */
    @Query(value = "select yxp.id, yxp.store_name AS storeName,yxc.cate_name as cateName,yxp.price from yx_store_product yxp" +
            " inner join yx_store_category yxc on yxp.cate_id = yxc.id where if(?1!='',yxp.store_name=?1,1=1)" +
            " and if(?2!='',yxp.cate_Id in(?3),1=1) and yxp.is_del=0 and yxp.id not in (" +
            " select yxs.product_id from yx_store_promote yx inner join yx_store_promote_product yxs on yx.id = yxs.promote_id" +
            " where yx.begin_time >= DATE_FORMAT(?4,\"%Y-%m-%d %H:%i:%S\") " +
            " and  yx.end_time <= DATE_FORMAT(?5,\"%Y-%m-%d %H:%i:%S\")" +
            " ) order by yxp.id desc",
            countQuery = "select count(*) from yx_store_product yxp" +
                    " inner join yx_store_category yxc on yxp.cate_id = yxc.id where if(?1!='',yxp.store_name=?1,1=1)" +
                    " and if(?2!='',yxp.cate_Id in(?3),1=1) and yxp.is_del=0 and yxp.id not in (" +
                    " select yxs.product_id from yx_store_promote yx inner join yx_store_promote_product yxs on yx.id = yxs.promote_id" +
                    " where yx.begin_time >= DATE_FORMAT(?4,\"%Y-%m-%d %H:%i:%S\") " +
                    " and  yx.end_time <= DATE_FORMAT(?5,\"%Y-%m-%d %H:%i:%S\")" +
                    " ) ",nativeQuery = true)
    Page<Map> getPromoteProduct(String storeName, String cateLogo, List<Integer> cateId, String beginTime, String endTime, Pageable pageable);
    /**
     * 查询商品表中的品牌
     * @return
     */
    @Query(value = "select yx.brand_name as brandName from yx_store_product yx where yx.brand_name is not null GROUP BY yx.brand_name",nativeQuery = true)
    List<String> getListBrandName();
}